package com.wangfugui.mybatisplus.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.util.StringUtils;
import com.wangfugui.mybatisplus.domain.Equity;
import com.wangfugui.mybatisplus.domain.Position;
import com.wangfugui.mybatisplus.domain.Transaction;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;


public class ExcelUtils {

	public static List<Map<Integer,String>> readExcel(InputStream inputStream){
		//该监听器一行行将文件读取到内存
		ExcelListener excelListener =new ExcelListener();
		 ExcelReader excelReader = null;
	        try {
		        //把前端传来的文件转成输入流

	            // 初始化对象
	            excelReader = EasyExcel.read(inputStream, excelListener)
                    .extraRead(CellExtraTypeEnum.MERGE)
                    .headRowNumber(0)
                    .build();

	            // 指定读取哪一个sheet
	            ReadSheet readSheet = EasyExcel.readSheet(0).build();
	            // 开始读取
	            excelReader.read(readSheet);
	            List<Map<Integer,String>> list = excelListener.getExcelList();
                AtomicReference<String> temp = new AtomicReference<>(list.get(0).get(0));
                list.stream().forEach(e->{
                    if (e.get(0)==null){
                        e.put(0,temp.get());
                    }else {
                        temp.set(e.get(0));
                    }
                });
	            return list;
	        } catch (Exception e) {
	            throw new RuntimeException("文件错误");

	        } finally {
	            if (excelReader != null) {
	                // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
	                excelReader.finish();
	            }

	        }
	}
	public static void main(String[] args) throws FileNotFoundException {
		String filePath = "/Users/zhaolele/Documents/mail/FUTU_Daily_Report20250610.xlsx";
		InputStream in =new FileInputStream(new File(filePath));
		List<Map<Integer,String>> list = readExcel(in);

		AtomicReference<String> temp = new AtomicReference<>(list.get(0).get(0));
		list.stream().forEach(e->{
			if (e.get(0)==null){
				e.put(0,temp.get());
			}else {
				temp.set(e.get(0));
			}
		});
		list.stream().forEach(x->System.err.println(x));
	}

    public static List<Transaction> getTransactionList(List<Map<Integer,String>> list){
        List<Map<Integer, String>> tradeReportList = list.stream()
                .filter(e -> e.get(0) != null && e.get(0).equals("trade report")).collect(Collectors.toList());
        if (tradeReportList == null || tradeReportList.size() <= 2) {
            return new ArrayList<>(); // 如果列表为空或只有两个元素，直接返回空列表
        }
        List<Map<Integer, String>> subList = tradeReportList.subList(1, tradeReportList.size() - 1);
        List<Transaction> transactionList = new ArrayList<>();
        Transaction transaction = null;
        Optional<Map<Integer, String>> baseCurrencyOptional =
            list.stream().filter(e -> e.get(0) != null && e.get(0).equals("Base Currency")).findFirst();
        String baseCurrency = "";
        if (baseCurrencyOptional.isPresent()){
            baseCurrency = baseCurrencyOptional.get().get(1);
        }
        for (Map<Integer, String> map : subList) {
            transaction = new Transaction();
            transaction.setTradeDate(map.get(5));
            transaction.setSettlementDate(map.get(6));
            String buyOrSell = map.get(12);
            if ("Buy".equals(buyOrSell)){
                transaction.setBs("B");
            }else {
                transaction.setBs("S");
            }
            transaction.setQty(map.get(14));
            transaction.setStockCode(map.get(11));
            //TODO 手工维护
            transaction.setIsin("手工维护");
            transaction.setSecurityName("手工维护");

            transaction.setUnderlyingCcy(map.get(9));
            BigDecimal grossPriceLocalCCY = new BigDecimal(map.get(13));
            transaction.setGrossPriceLocalCCY(grossPriceLocalCCY.toString());

//            I - FUTU-TR（R+S+T）/ D
            BigDecimal R = new BigDecimal(map.get(17));
            BigDecimal S = new BigDecimal(map.get(18));
            BigDecimal T = new BigDecimal(map.get(19));
            //汇率*N
//            BigDecimal netPriceLocalCCY = ;
            String currency = map.get(9);
            if ("USD".equals(currency)){
                transaction.setFx("1.00");
            }else {
                transaction.setFx(getFx(list, currency));
            }

            transaction.setSettleCCY(baseCurrency);
            String fx = "1";
            if (!"USD".equals(baseCurrency)){
                fx = getFx(list,baseCurrency);
            }
            BigDecimal RST = R.add(S).add(T);
            if (!StringUtils.isBlank(fx)){
//                BigDecimal grossPriceSettleCCY = new BigDecimal(fx).multiply(grossPriceLocalCCY).setScale(2, BigDecimal.ROUND_HALF_UP);
//                transaction.setGrossPriceSettleCCY(grossPriceSettleCCY);
////                BigDecimal netPriceSettleCCY = new BigDecimal(fx).multiply(netPriceLocalCCY).setScale(2, BigDecimal.ROUND_HALF_UP);
//                BigDecimal grossAmountSettleCCY = grossPriceSettleCCY.multiply(transaction.getQty()).setScale(2, BigDecimal.ROUND_HALF_UP);
//                transaction.setGrossAmountSettleCCY(grossAmountSettleCCY);
//                BigDecimal netAmountSettleCCY = grossAmountSettleCCY.subtract(RST);
//                transaction.setNetAmountSettleCCY(netAmountSettleCCY);
//                transaction.setNetPriceSettleCCY(netAmountSettleCCY.divide(transaction.getQty(),2,BigDecimal.ROUND_HALF_UP));
            }

//            transaction.setCounterpartyFeeCentsPerShare(RST.setScale(2, BigDecimal.ROUND_HALF_UP));
            transaction.setWithholdTaxSettlementCcy(T.toPlainString());
            //TODO 判断开仓平仓
            transaction.setOpenOrUnwind("");
            transaction.setMarginOrFFSOrPTH(map.get(4));
//            transaction.setNetPriceLocalCCY(new BigDecimal(fx).multiply(transaction.getNetPriceSettleCCY()));
            transactionList.add(transaction);
        }
        return transactionList;
    }

    public static List<Equity> getEquityList(List<Map<Integer,String>> list){
        List<Map<Integer, String>> holdingsReport = list.stream().filter(e -> e.get(0) != null && e.get(0).equals("Holdings report")).collect(Collectors.toList());
        if (holdingsReport == null || holdingsReport.size() <= 2) {
            return new ArrayList<>(); // 如果列表为空或只有两个元素，直接返回空列表
        }
        List<Map<Integer, String>> subList = holdingsReport.subList(1, holdingsReport.size() - 1);
        List<Equity> equityList = new ArrayList<>();
        Equity equity ;
        for (Map<Integer, String> map : subList) {
            equity = new Equity();
            equity.setMarket(map.get(7));
            equity.setTicker(map.get(5));
            equity.setInvestmentName(map.get(6));
            //TODO 手工维护,从同花顺获取交易所名称
            equity.setExchangeName("手工维护");
            equity.setQuantity(map.get(12));
            equity.setUnrealizedDailyPNLUSD(map.get(15));
            //TODO 待完善,从数据库获取数据汇总
            equity.setMtdPNLUnrealised("0");
            equity.setMtdPNLRealised("0");
            equity.setYtdPNL("0");
            equity.setItdPNL("0");
            equityList.add(equity);

        }
        return equityList;
    }
    public static List<Position> getPositionList(List<Map<Integer,String>> list){
        List<Map<Integer, String>> holdingsReport = list.stream()
                .filter(e -> e.get(0) != null && e.get(0).equals("Holdings report")).collect(Collectors.toList());
        if (holdingsReport == null || holdingsReport.size() <= 2) {
            return new ArrayList<>(); // 如果列表为空或只有两个元素，直接返回空列表
        }
        List<Map<Integer, String>> subList = holdingsReport.subList(1, holdingsReport.size() - 1);
        List<Position> positions = new ArrayList<>();
        String portfolioValue = getPortfolioValue(list);
        Position position ;
        for (Map<Integer, String> map : subList) {
            position = new Position();
            //TODO 手工维护
            position.setSector("");
            position.setTicker(map.get(5));
            position.setQuantity(map.get(12));
            position.setCost(map.get(14));
            position.setMarketPriceLocalCCY(map.get(11));
            //TODO 数据库查询
            position.setPriceBeginningOfYear("0");
            position.setMarketValueLocalCCY(map.get(13));
            //TODO (损益表E / 持仓表D) %
            position.setUnrealizedPNLAsAPercentageOfCost("0");
            //TODO 持仓表H / 持仓表总敞口 (102行)
            position.setExposureAsAPercentageOfGrossExposure("0");
            position.setUnderlierLocalCCY(map.get(8));
            String fx = getFx(list, map.get(8));
            position.setExchangeRate(fx);
            BigDecimal gm = new BigDecimal(position.getExchangeRate()).multiply(new BigDecimal(position.getMarketValueLocalCCY())).setScale(2, RoundingMode.HALF_UP);
            //G*M
            position.setMarketValueUSD(gm.toPlainString());

            //H / FUTU-VR-I (美元)
            position.setMarketValueUSDAsAPercentageOfBeginOfMonthNAV(gm.divide(new BigDecimal(portfolioValue),2,RoundingMode.HALF_UP).toPlainString());
            positions.add(position);
        }
        return positions;
    }
    public static String getFx(List<Map<Integer,String>> list, String currency){
        List<Map<Integer, String>> exchangeRateReport = list.stream()
                .filter(e -> e.get(0) != null && e.get(0).equals("Exchange Rate Report")).collect(Collectors.toList());
        if (exchangeRateReport == null || exchangeRateReport.size() <= 1){
            return "1";
        }
        List<Map<Integer, String>> subList = exchangeRateReport.subList(1, exchangeRateReport.size());
        for (Map<Integer, String> map : subList) {
            if (map.get(2).startsWith(currency)){
                return map.get(3);
            }
        }
        return "1";
    }
    public static <T> List<T> removeFirstAndLast(List<T> list) {

        return list.subList(1, list.size() - 1); // 去除首尾元素
    }

    public static String getPortfolioValue(List<Map<Integer,String>> list){
        Optional<Map<Integer, String>> baseCurrency =
            list.stream().filter(e -> e.get(0) != null && e.get(0).equals("Base Currency")).findFirst();
        if (!baseCurrency.isPresent()){
            return "";
        }
        String baseCurrencyValue = baseCurrency.get().get(1);
        List<Map<Integer, String>> netAssetValueReport = list.stream().filter(e -> e.get(0) != null && e.get(0).equals("Net Asset Value Report")).collect(Collectors.toList());
        if (netAssetValueReport == null || netAssetValueReport.size() <= 1){
            return "";
        }
        List<Map<Integer, String>> subList = netAssetValueReport.subList(1, netAssetValueReport.size());
        for (Map<Integer, String> map : subList) {
            if (map.get(5).equals(baseCurrencyValue)){
                return map.get(8);
            }
        }
        return "";
    }
}

